MacTech 1 to 12/MacTech-vol-1-12.toast/Source/MacTech® Magazine/Volume 10 - 1994/10.07 Jul 94/FoxProAndAppleScript

Text File | 1994-06-16 | 6.3 KB | 280 lines | [TEXT/MSWD]

tell application “Microsoft FoxPro”
  DO SCRIPT “WAIT WINDOW ¬
    ‘This is my first script’ TIMEOUT 2”
end tell

________________________

-- Sample AppleScript showing Required commands.
run application "Microsoft FoxPro"
tell application "Microsoft FoxPro"
  activate "Microsoft FoxPro"
  open "config.fpm"
  Do Script "=INKEY(2)"
  activate "Finder"
  Quit
end tell

_______________________

* IS_ASCRIPT.PRG

#DEFINE C_ASFILE "AppleScript™"
PRIVATE hasascript,asfilename
PRIVATE saveerr,savelib,haderr

m.haderr = .F.
m.hasascript = .T.
m.asfilename = ""

m.savelib = SET('LIBRARY')
IF ATC('FOXTOOLS',m.savelib)=0
  m.saveerr = ON('ERROR')
  ON ERROR m.haderr = .T.
  SET LIBRARY TO FOXTOOLS ADDITIVE
  ON ERROR &saveerr
  IF m.haderr
    WAIT WINDOW ;
      "Could not load FoxTools library"
    RETURN .F.
  ENDIF
ENDIF

m.asfilename =fxsystem(1)+':'+C_ASFILE
m.hasascript = FILE(m.asfilename)

IF ATC('FOXTOOLS',m.savelib)=0
  RELEASE LIBRARY FOXTOOLS
ENDIF

RETURN m.hasascript

___________________

* Mac Sales program

m.filepath = SET("DEFAULT") +;
  "MACHETE:APPLESCRIPT:"
m.salesdbf = m.filepath + "MAC SALES.DBF"
m.xlsheet  = m.filepath + "XL SALES.XLS"
m.xlscript = m.filepath + "XL SCRIPT"

SET SAFETY OFF
CLOSE DATA

USE (m.salesdbf) ALIAS macsales AGAIN

SELECT product,unitprice*quantity AS sales,;
  YEAR(date) AS Year FROM macsales ;
  GROUP BY year,product ORDER BY year;
  INTO CURSOR xlcurs

COPY TO (m.xlsheet) TYPE XLS

RUNSCRIPT (m.xlscript) TO m.retval

__________________

-- XL Script is file name
tell application "Microsoft Excel"

  -- setup AppleScript variables
  set x to path to startup disk
  copy (x as string) ¬
    & "Machete:AppleScript:" ¬
    to foxpath
  copy "XL Macros" to xlmacs
  -- FoxPro query file
  copy "XL SALES.XLS" to xlsales

  -- open files if not already open
  if not (Window xlmacs exists) then
    open foxpath & xlmacs
  end if
  if not (Window xlsales exists) then
    open foxpath & xlsales
  end if

  -- make sure xlsales document is on top
  set the selection of Document ¬
    xlsales to "R1C1"

  -- run excel macro to create cross tab
  Evaluate "run('" & xlmacs & "'!Cross_Tab)"

  -- close files, see if Macro file is hidden 1st
  close Window xlsales saving no
  if visible of Window xlmacs = false then
    set visible of Window xlmacs to true
  end if
  close Window xlmacs saving no

  --bring Excel to front
  activate "Microsoft Excel"

end tell

______________________

cross tab
=ACTIVATE("XL SALES.XLS")
=SELECT("C1:C3")
=SET.DATABASE()
=CROSSTAB.CREATE({"product",0,"Auto","Auto","NNNNNNN"},{"year",0,"Auto","Auto","NNNNNNN"},{"sales","SUM(sales)","YNNNN",FALSE},TRUE,TRUE,1,TRUE,TRUE)
=SELECT("crosstab_range")
=FORMAT.AUTO(13,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE)
=APPLY.STYLE("Currency [0]")
=COLUMN.WIDTH(13,"C1")
=COLUMN.WIDTH(10,"C2:C10")
=FORMULA("=CROSSTAB(""Sales"",""Summary:"",values_array,TRUE,TRUE,1,TRUE)")
=SELECT("R1C1")
=RETURN("OK")

Table A. Excel Macro to create Cross Tab from XL Macros file.
______________________
-- Zip Back script using Mark Alldritt’s
-- Script Tools scripting extensions.
-- prompt for backup method
display dialog "What do you want to archive?" ¬
  buttons {"Files", "Folders"}

copy (button returned of the result = "Folders") ¬
  to isfolders

-- get list of files/folders
if isfolders then
  copy (choose several folders) to flist
else
  copy (choose several files) to flist
end if

-- get backup archive name
copy (choose new file default name "backup.zip" ¬
  with prompt "Save archive as:") to tmprec
copy ((folder returned of tmprec as string) & ¬
  filename returned of tmprec) to zipFileName

tell application "ZipIt"

  New window
  activate "ZipIt"

  repeat with myitem in flist
    Add file (myitem as string)
  end repeat

  with timeout of 3600 seconds
    Compress to file zipFileName
  end timeout

  quit
  return zipFileName
end tell

________________

* FoxPro Regions program

#DEFINE tab CHR(9)
#DEFINE crlf CHR(13)+CHR(10)

m.filepath = SET("DEFAULT") + ;
  "MACHETE:APPLESCRIPT:"
m.salesdbf = m.filepath + "MAC SALES.DBF"
m.xlscript = m.filepath + "Create Chart"
m.tmpstr = " "

CLOSE DATA

USE (m.salesdbf) ALIAS macsales AGAIN

SELECT DISTINCT region FROM macsales ;
  INTO ARRAY rgnarr
SELECT DISTINCT YEAR(date) AS Year FROM macsales;
  ORDER BY 1;
  INTO ARRAY yeararr

* create string for clipboard
FOR i = 1 TO ALEN(yeararr)
  m.tmpstr=m.tmpstr + tab + ;
    ALLTRIM(STR(yeararr(m.i)))
ENDFOR
m.tmpstr=m.tmpstr + crlf

FOR i = 1 TO ALEN(rgnarr)
  m.tmpstr=m.tmpstr + rgnarr(m.i) +tab
  FOR j = 1 TO ALEN(yeararr)
    SUM unitprice*quantity TO m.tmpsum ;
      FOR region = rgnarr(m.i) AND ;
      YEAR(date)=yeararr(m.j)
    m.tmpstr=m.tmpstr + STR(m.tmpsum) + ;
      IIF(j=ALEN(yeararr),'',tab)
  ENDFOR
  m.tmpstr=m.tmpstr + crlf
ENDFOR

_CLIPTEXT = m.tmpstr
RUNSCRIPT (m.xlscript) TO retval



--AppleScript script
tell application "Microsoft Excel"

  -- Set up variables
  set charttitle to "Annual Sales by Region"
  set charttype to three D column
  set chartlegend to true

  -- Now do the work
  activate "Microsoft Excel"
  make Document
  Evaluate "Paste()" --paste contents of clipboard
  Evaluate "APPLY.STYLE(\"Comma [0]\")"

  (* this repeat loop is used to convert Excel
  numbers into labels for the series headings *)
  repeat with snum from 2 to 99 -- use large number
    copy value of Cell snum to scontents
    if scontents is equal to "" then
      exit repeat
    end if
    try
      get scontents as integer -- check for number
      set the value of Cell snum to "'" & scontents
      on error -- loop around
    end try
  end repeat

  make new Chart
  set type of first Chart to charttype
  set variant of first Chart to 1
  set has legend of first Chart to chartlegend
  set title of first Chart to charttitle

  copy first Chart to finalchart

  close first Document saving no
  close first Document saving no
  quit --not needed if you have enough memory
end tell

(* Need to launch WP first if not already running,
else the chart gets lost. Also, make sure you have
stationery file "Machete" loaded. *)
run application "WordPerfect"
tell application "WordPerfect"
  ignoring application responses
    activate "WordPerfect"
    -- this is a stationery file
    open (a reference to file "Machete")
    make new paragraph with data finalchart
    move paragraphs 2 thru 14 to front
  end ignoring
end tell